package com.harmontronics.hanma.paas.paas.domain.app.service.dialect;

import cn.hutool.core.util.PageUtil;
import com.harmontronics.hanma.paas.paas.domain.app.entity.valueobject.ViewDataBaseTypeEnum;
import com.harmontronics.hanma.paas.paas.domain.app.repository.persistence.HmDatasourceEnvRepository;
import com.harmontronics.hanma.paas.paas.domain.app.repository.po.HmModelPO;
import com.harmontronics.hanma.paas.paas.domain.app.service.HmPublishedEntityModelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.util.Pair;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author ldb
 */
@Service
public class SqlServerDialectService extends AbstractDialectService {
    private String URL_TEMPLATE = "jdbc:%s://%s:%s;DatabaseName=%s";

    //此语法只支持2012以后版本
    private static final String PAGE_LIST_TEMPLATE = "SELECT %s FROM %s WHERE %s %s offset %s rows " +
            "fetch next %s rows only";

    @Autowired
    private HmDatasourceEnvRepository hmDatasourceEnvRepository;

    @Autowired
    private HmPublishedEntityModelService hmPublishedEntityModelService;

    @Override
    public HmDatasourceEnvRepository getHmDatasourceEnvRepository() {
        return hmDatasourceEnvRepository;
    }

    @Override
    public HmPublishedEntityModelService getHmPublishedEntityModelService() {
        return hmPublishedEntityModelService;
    }


    @Override
    protected String concatUrl(String code, String host, Integer port, String dbName) {
        return String.format(URL_TEMPLATE,code,host,port,dbName);
    }

    @Override
    public ViewDataBaseTypeEnum getDataBaseType() {
        return ViewDataBaseTypeEnum.MSSQL;
    }

    private static final String VIEW_INFO_SQL = "SELECT\n" +
            "\tc.name AS ColumnsName,\n" +
            "\tt.name AS ColumnsType \n" +
            "FROM\n" +
            "\tSysObjects AS o,\n" +
            "\tSysColumns AS c,\n" +
            "\tSysTypes AS t \n" +
            "WHERE\n" +
            "\to.type IN ( 'u', 'v' ) \n" +
            "\tAND o.id = c.id \n" +
            "\tAND c.xtype = t.xtype \n" +
            "\tAND o.Name = ?";

    @Override
    public List<ViewColumn> getViewColumns(Long datasourceId, String viewName) {
        Connection connection =null;
        try {
            Pair<String,Connection> pair= getConnection(datasourceId);
            connection = pair.getSecond();
            PreparedStatement ps = connection.prepareStatement(VIEW_INFO_SQL);
            ps.setString(1,viewName);
            ResultSet rs = ps.executeQuery();
            List<ViewColumn> viewColumns = new ArrayList<>();
            while (rs.next()) {
                String type = rs.getString(2);
                String name = rs.getString(1);
                viewColumns.add(new ViewColumn(name,type));
            }
            rs.close();
            return viewColumns;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }finally {
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    @Override
    protected String applyPageListSql(HmModelPO modelPO, Map<String, Object> params, List<String> orderRules, Integer pageNo, Integer pageSize) {
        String columns = getSelectColumns(modelPO);
        String viewName = modelPO.getViewName();
        String whereStr = getWhereStr(params);
        String orderBy = getOrderByStr(orderRules);
        int start = PageUtil.getStart(pageNo,pageSize);
        return String.format(PAGE_LIST_TEMPLATE, columns, viewName, whereStr, orderBy,start,pageSize);
    }
}
